﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[vwWH_Perfmon]
#-- Purpose:		Provides for a human friendly view of the data in tblWH_Perfmon
#--	Last Update:	02/14/2017
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE VIEW [dbo].[vwWH_Perfmon]
AS
SELECT		new.server_ssd_id,
			new.date_inserted,
			new.buffer_cache_hit_ratio,
			new.page_life_expectancy,
			(new.target_server_memory_KB * 300 / 1024 / 4096) minimum_recommended_page_life_expectancy,		--- 300 per 4 GB (https://simplesqlserver.com/2013/08/19/fixing-page-life-expectancy-ple/)
			CAST(CAST((CAST(new.logins_per_sec as float) - old.logins_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 logins_per_sec, 
			CAST(CAST((CAST(new.logouts_per_sec as float) - old.logouts_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 logouts_per_sec, 
			new.user_connections,
			new.optimizer_memory_KB,
			new.sql_cache_memory_KB,
			new.target_server_memory_KB,
			new.total_server_memory_KB,
			CAST(CAST((CAST(new.sql_compilations_per_sec as float) - old.sql_compilations_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 sql_compilations_per_sec, 
			CAST(CAST((CAST(new.sql_recompilations_per_sec as float) - old.sql_recompilations_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 sql_recompilations_per_sec, 
			CAST(CAST((CAST(new.batch_requests_per_sec as float) - old.batch_requests_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 batch_requests_per_sec, 
			CAST(CAST((CAST(new.number_of_deadlocks_per_sec as float) - old.number_of_deadlocks_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 number_of_deadlocks_per_sec, 
			CAST(CAST((CAST(new.average_wait_time_ms as float) - old.average_wait_time_ms) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 average_wait_time_ms, 
			CAST(CAST((CAST(new.lock_waits_per_sec as float) - old.lock_waits_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 lock_waits_per_sec, 
			CAST(CAST((CAST(new.lock_wait_time_ms as float) - old.lock_wait_time_ms) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 lock_wait_time_ms, 
			CAST(CAST((CAST(new.full_scans_per_sec as float) - old.full_scans_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 full_scans_per_sec, 
			CAST(CAST((CAST(new.lazy_writes_per_sec as float) - old.lazy_writes_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 lazy_writes_per_sec, 
			CAST(CAST((CAST(new.page_reads_per_sec as float) - old.page_reads_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 page_reads_per_sec, 
			CAST(CAST((CAST(new.page_writes_per_sec as float) - old.page_writes_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 page_writes_per_sec, 
			CAST(CAST((CAST(new.checkpoint_pages_per_sec as float) - old.checkpoint_pages_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 checkpoint_pages_per_sec, 
			ISNULL(CAST(CAST((CAST(new.page_splits_per_sec as float) - old.page_splits_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100, 0) page_splits_per_sec, 
			CAST(CAST((CAST(new.transactions_per_sec as float) - old.transactions_per_sec) / DATEDIFF(ss, old.date_inserted, new.date_inserted) * 100 as bigint) as float) / 100 transactions_per_sec, 
			(CASE WHEN (new.idle - old.idle + new.cpu_busy - old.cpu_busy) > 0
				THEN CAST(CAST(((CAST(new.cpu_busy as float) - old.cpu_busy) / (new.idle - old.idle + new.cpu_busy - old.cpu_busy) * 1.00) * 10000 as bigint) as float) / 100 
				ELSE 0 END) cpu_busy, 
			(CASE WHEN (new.idle - old.idle + new.cpu_busy - old.cpu_busy) > 0
				THEN CAST(CAST(((CAST(new.io_busy as float) - old.io_busy) / (new.idle - old.idle + new.io_busy - old.io_busy) * 1.00) * 10000 as bigint) as float) / 100 
				ELSE 0 END) io_busy 
FROM		[dbo].[tblWH_Perfmon] new
JOIN		[dbo].[tblWH_Perfmon] old ON new.server_ssd_id = old.server_ssd_id
			AND new.[rownum] = (old.[rownum]+1)
WHERE		DATEDIFF(ss, old.date_inserted, new.date_inserted) > 0
